package com.stm.bi.report.productTrend;

import com.stm.bi.core.util.DateUtils;
import com.stm.bi.core.util.ETLUtils;
import com.stm.bi.core.util.SqlUtils;
import com.stm.bi.dto.query.ChartReportQry;
import com.stm.bi.dto.query.DimensionDataGroupQry;
import com.stm.bi.dto.query.GroupField;
import com.stm.bi.dto.query.TableReportQry;
import com.stm.bi.dto.query.ods.OdsListQry;
import com.stm.bi.model.Chart;
import com.stm.bi.model.DatatablePage;
import com.stm.bi.model.RowData;
import com.stm.bi.model.Title;
import com.stm.bi.repository.dataobject.ods.OdsDeptDO;
import com.stm.bi.repository.dataobject.ods.OdsWarehouseDO;
import com.stm.bi.repository.ods.OdsDeptRepository;
import com.stm.bi.repository.ods.OdsWarehouseRepository;
import com.stm.framework.core.context.UserHolder;
import com.stm.framework.dbcp.JdbcService;
import com.stm.framework.dto.PageQuery;
import com.stm.saas.dto.query.WarehousePageByOrgIdQry;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

import static com.stm.bi.constant.IndexConstants.DATE_FIELD;
import static com.stm.bi.constant.PlanConstants.TYPE_MONTH;
import static com.stm.bi.constant.PlanConstants.TYPE_WEEK;
import static com.stm.bi.report.productTrend.ProductDataQueryExe.DELAY;

@Component
public class StyleQtyReportQueryExe {

    @Resource
    private JdbcService jdbcService;
    @Resource
    private OdsWarehouseRepository odsWarehouseRepository;
    @Resource
    private OdsDeptRepository odsDeptRepository;


    private final List<String> ITEMS = Arrays.asList("正价销量","特价销量","库存","售罄率","款数构成累计比");
    public Chart<List<List<Object>>> styleQtyChart(ChartReportQry reportQry) {
        String dateType = reportQry.getDateType().getValue();
        List<String> dates = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), dateType, DELAY);

        List<GroupField> groupFields = new ArrayList<>();
        groupFields.add(GroupField.of("positive_sales_volume", "sum"));
        groupFields.add(GroupField.of("special_sales_volume", "sum"));


        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setDeptId(reportQry.getDeptId())
                .setSalesType(reportQry.getSalesType())
                .setDateType(dateType)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setOrderBy("date")
                .setGroupBy(List.of("date", "style_code"))
                .setGroupFields(groupFields);

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");

        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        DimensionDataGroupQry invQry = new DimensionDataGroupQry()
                .setDeptId(getDeptIdMap(UserHolder.getOrgId()).get(reportQry.getDeptId()))
                .setDateType(dateType)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setGroupBy(List.of("date"))
                .setGroupFields(List.of(GroupField.of("inventory", "sum")));

        String invSql = SqlUtils.buildSql(invQry, "ads_base_product_inventory");

        List<Map> invMaps = jdbcService.selectList(invSql, new Object[]{});

        BigDecimal total = BigDecimal.valueOf(maps.stream()
                .map(m -> m.get("style_code"))
                .distinct()
                .count());

        return getChart(dates, maps, invMaps, dateType, total);

    }



    private Chart<List<List<Object>>> getChart(List<String> dates,
                                               List<Map> maps,
                                               List<Map> invMaps,
                                               String dateType,
                                               BigDecimal total) {
        Chart<List<List<Object>>> chart = new Chart<>();
        chart.getItems().addAll(ITEMS);
        for(String date: dates){
            List<Object> list = new ArrayList<>();
            list.add(DateUtils.getReportDateName(date, dateType));

            List<Map> dateMaps = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                    .toList();

            BigDecimal positiveSalesVolume = dateMaps.stream()
                    .map(m -> (BigDecimal) m.get("positive_sales_volume"))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal specialSalesVolume = dateMaps.stream()
                    .map(m -> (BigDecimal) m.get("special_sales_volume"))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal salesStyles = BigDecimal.valueOf(maps.stream()
                    .filter(m -> date.compareTo((String) m.get("date")) >= 0)
                    .map(m -> m.get("style_code"))
                    .distinct()
                    .count());

            Map invMap = invMaps.stream()
                    .filter(m -> Objects.equals(m.get("date"), date))
                    .findAny().orElse(new HashMap());
            BigDecimal qty = positiveSalesVolume.add(specialSalesVolume);
            BigDecimal inventory = invMap.get("inventory") == null ? BigDecimal.ZERO : (BigDecimal) invMap.get("inventory");
            BigDecimal digestibility = qty.add(inventory).doubleValue() == 0 ? BigDecimal.ZERO : qty.multiply(BigDecimal.valueOf(100)).divide(qty.add(inventory), 2, RoundingMode.HALF_UP);

            list.add(specialSalesVolume);
            list.add(positiveSalesVolume);
            list.add(inventory);
            list.add(digestibility);
            list.add(total.doubleValue() == 0 ? BigDecimal.ZERO : salesStyles.multiply(BigDecimal.valueOf(100)).divide(total, 2, RoundingMode.HALF_UP));
            chart.getDatas().add(list);
        }

//        List<List<Object>> t = new ArrayList<>();
//        total.add(Arrays.asList(POSITIVE_SALES_VOLUME_INDEX_NAME, positiveSalesVolumeSum, null, 0));
//        total.add(Arrays.asList(SPECIAL_SALES_VOLUME_INDEX_NAME, specialSalesVolumeSum, null, 0));
//        if(sumQty.doubleValue() + inventory.doubleValue() != 0){
//            total.add(Arrays.asList(DIGESTIBILITY_INDEX_NAME, sumQty.divide(sumQty.add(inventory),2, RoundingMode.HALF_UP) + "%", null, 1));
//        }else {
//            total.add(Arrays.asList(DIGESTIBILITY_INDEX_NAME, "100.00%", null, 1));
//        }

        return chart;
    }


    public DatatablePage styleQtyTable(TableReportQry reportQry) {
        DatatablePage table = new DatatablePage();

        String dateType = reportQry.getDateType().getValue();
        List<String> dates = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), dateType, 2);
        List<Title> titles = new ArrayList<>();

        if(TYPE_WEEK.equals(dateType)){
            titles.add(Title.of("date","周"));
        }else {
            titles.add(Title.of("date","月"));
        }

        titles.add(Title.of("positive_sales_volume","正价销量"));
        titles.add(Title.of("special_sales_volume","特价销量"));
        titles.add(Title.of("inventory","库存"));
        titles.add(Title.of("sales_styles", "销售款数"));
        titles.add(Title.of("total_styles", "总款数"));
        titles.add(Title.of("proportion", "款式构成累计比"));
        titles.add(Title.of("digestibility", "售罄率"));

        List<GroupField> groupFields = new ArrayList<>();
        groupFields.add(GroupField.of("positive_sales_volume", "sum"));
        groupFields.add(GroupField.of("special_sales_volume", "sum"));

        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setDeptId(reportQry.getDeptId())
                .setSalesType(reportQry.getSalesType())
                .setDateType(dateType)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setOrderBy("date")
                .setGroupBy(List.of("date", "style_code"))
                .setGroupFields(groupFields);

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");

        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        DimensionDataGroupQry invQry = new DimensionDataGroupQry()
                .setDeptId(getDeptIdMap(UserHolder.getOrgId()).get(reportQry.getDeptId()))
                .setDateType(dateType)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setGroupBy(List.of("date"))
                .setGroupFields(List.of(GroupField.of("inventory", "sum")));

        String invSql = SqlUtils.buildSql(invQry, "ads_base_product_inventory");

        List<Map> invMaps = jdbcService.selectList(invSql, new Object[]{});

        BigDecimal sumPositiveSalesVolume = maps.stream()
                .map(m -> (BigDecimal) m.get("positive_sales_volume"))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        BigDecimal sumSpecialSalesVolume =  maps.stream()
                .map(m -> (BigDecimal) m.get("special_sales_volume"))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        BigDecimal sumQty = sumPositiveSalesVolume.add(sumSpecialSalesVolume);
        BigDecimal totalStyles = BigDecimal.valueOf(maps.stream()
                .map(m -> m.get("style_code"))
                .distinct()
                .count());

        BigDecimal sumInventory = invMaps.stream()
                .filter(m -> Objects.equals(m.get("date"), dates.getLast()))
                .map(m -> (BigDecimal) m.get("inventory"))
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        List<RowData> datas = new ArrayList<>();
        for(String date: dates){
            List<Map> dateMaps = maps.stream()
                    .filter(m -> Objects.equals(m.get("date"), date))
                    .toList();

            BigDecimal positiveSalesVolume = dateMaps.stream()
                    .map(m -> (BigDecimal) m.get("positive_sales_volume"))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal specialSalesVolume = dateMaps.stream()
                    .map(m -> (BigDecimal) m.get("special_sales_volume"))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal salesStyles = BigDecimal.valueOf(maps.stream()
                    .filter(m -> date.compareTo((String) m.get("date")) >= 0)
                    .map(m -> m.get("style_code"))
                    .distinct()
                    .count());

            Map invMap = invMaps.stream()
                    .filter(m -> Objects.equals(m.get("date"), date))
                    .findAny().orElse(new HashMap());
            BigDecimal qty = positiveSalesVolume.add(specialSalesVolume);
            BigDecimal inventory = invMap.get("inventory") == null ? BigDecimal.ZERO : (BigDecimal) invMap.get("inventory");
            BigDecimal digestibility = qty.add(inventory).doubleValue() == 0 ? BigDecimal.ZERO : qty.multiply(BigDecimal.valueOf(100)).divide(qty.add(inventory), 2, RoundingMode.HALF_UP);

            RowData data = new RowData();
            if(TYPE_WEEK.equals(dateType)){
                data.put("date", DateUtils.getWeekName(DateUtils.getWeek(date)));
            }else {
                data.put("date", DateUtils.getNameByCycle(date, TYPE_MONTH));
            }

            data.put("positive_sales_volume", positiveSalesVolume + "");
            data.put("special_sales_volume", specialSalesVolume + "");
            data.put("inventory", inventory + "");
            data.put("sales_styles", dateMaps.size() + "");
            data.put("total_styles", totalStyles + "");
            data.put("proportion", totalStyles.doubleValue() == 0 ? BigDecimal.ZERO : salesStyles.multiply(BigDecimal.valueOf(100)).divide(totalStyles, 2, RoundingMode.HALF_UP) + "%");
            data.put("digestibility", digestibility + "%");

            datas.add(data);
        }

        RowData total = new RowData();
        total.put("date", "合计");
        total.put("positive_sales_volume", sumPositiveSalesVolume + "");
        total.put("special_sales_volume", sumSpecialSalesVolume + "");
        total.put("inventory", sumInventory + "");
        total.put("sales_styles", totalStyles + "");
        total.put("total_styles", totalStyles + "");
        total.put("proportion", "100%");
        total.put("digestibility", sumInventory.add(sumQty).doubleValue() == 0 ? "0%" : sumQty.multiply(BigDecimal.valueOf(100)).divide(sumInventory.add(sumQty), 2, RoundingMode.HALF_UP) + "%");
        datas.addFirst(total);

        datas = datas.subList(
                Math.min(datas.size(), (reportQry.getPageNum() - 1) * reportQry.getPageSize()),
                Math.min(datas.size(), reportQry.getPageNum() * reportQry.getPageSize())
        );


        table.setDatas(datas);
        table.setTotal(dates.size() + 1);
        table.setSize(reportQry.getPageSize());
        table.setCurrent(reportQry.getPageNum());
        table.setTitles(titles);

        return table;
    }

    private Map<Long, Long> getDeptIdMap(Long orgId) {

        OdsListQry qry = new OdsListQry();
        qry.setOrgId(orgId);
        //仓库与部门的对应关系逻辑
        List<OdsDeptDO> depts = odsDeptRepository.selectList(qry);
        Map<Long, Long> deptMap = new HashMap<>();
        depts.forEach(d -> deptMap.put(d.getId(), d.getParentId()));

        WarehousePageByOrgIdQry warehouseQry = new WarehousePageByOrgIdQry();
        warehouseQry.setOrgId(orgId);
        warehouseQry.setPageSize(PageQuery.MAX_PAGE_SIZE);
        List<OdsWarehouseDO> wareHouses = odsWarehouseRepository.selectList(qry);
        List<Long> deptIds = wareHouses.stream().map(OdsWarehouseDO::getDeptId).distinct().filter(Objects::nonNull).toList();

        //获取对应的仓库部门对应关系MAP
        return ETLUtils.getWarehouseDeptIdMap(deptMap, deptIds);
    }

}
